package edu.rit.hvz.android.DataLayer;

import java.util.ArrayList;
import java.util.List;

import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class PlayerDBAdapter {
    public static final String ROW_ID = "_id";
    public static final String NAME = "name";
    public static final String KILLCOUNT = "count";
    public static final String PASSWORD = "password";
    public static final String PICTURE = "picture";
    public static final String TAGGEDBY = "taggedBy";
    public static final String CLAN = "clan";
    public static final String TEAM = "status";

    private static final String DATABASE_TABLE = "player";
    private static final String CLAN_TABLE = "clan";

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, MainDBAdapter.DATABASE_NAME, null, MainDBAdapter.DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx
     *            the Context within which to work
     */
    public PlayerDBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the Players database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException
     *             if the database could be neither opened or created
     */
    public PlayerDBAdapter open() throws SQLException {
        this.mDbHelper = new DatabaseHelper(this.mCtx);
        this.mDb = this.mDbHelper.getWritableDatabase();
        return this;
    }

    /**
     * close return type: void
     */
    public void close() {
        this.mDbHelper.close();
    }


    /**
     * 
     * @param id
     * @param name
     * @param count
     * @param team
     * @param taggedBy
     * @param clan
     * @return
     */
    public long createPlayer(Long id, String name, Integer count, String team, Long taggedBy, Long clan){
        ContentValues initialValues = new ContentValues();
        initialValues.put(ROW_ID, id);
        initialValues.put(NAME, name);
        initialValues.put(KILLCOUNT, count);
        initialValues.put(TEAM, team);
        initialValues.put(TAGGEDBY, taggedBy);
        initialValues.put(CLAN, clan);
        long result = -1;
        try {
        result = this.mDb.insertOrThrow(DATABASE_TABLE, null, initialValues);
        
        } catch (SQLiteConstraintException e) {    
        	// The record already exists in the database, update it for changes
        	updatePlayer(id, name, count, team, taggedBy, clan);
        }
        
        return result;
        
    }
    
    public long createSquad(Long id, String name){
        ContentValues initialValues = new ContentValues();
        initialValues.put(ROW_ID, id);
        initialValues.put(NAME, name);  
        long result = -1;
        try {
        result = this.mDb.insertOrThrow(CLAN_TABLE, null, initialValues);
        } catch (SQLiteConstraintException e) {    
        	// Swallow the error. The database inserted a squad with an overlapping ID
        	// we expect this and it just means we already have the needed data.
        }
        
        return result;
    }

    /**
     * Delete the Player with the given rowId
     * 
     * @param rowId
     * @return true if deleted, false otherwise
     */
    public boolean deletePlayer(long rowId) {

        return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$
    }

    /**
     * Return a Cursor over the list of all Players in the database
     * 
     * @return Cursor over all Players
     */
    public Cursor getAllPlayers() {

    	String query = 
    			"select " + DATABASE_TABLE +"."+ROW_ID+", "+
    					DATABASE_TABLE +"."+NAME+", "+
    					DATABASE_TABLE +"."+KILLCOUNT+", "+
    					DATABASE_TABLE +"."+TEAM+", "+
    					DATABASE_TABLE +"."+TAGGEDBY+", "+
    					CLAN_TABLE +"."+NAME+" as clan "+
    					"from " + DATABASE_TABLE +" LEFT JOIN " + CLAN_TABLE + 
    					" On " + DATABASE_TABLE +"."+CLAN+" = " + CLAN_TABLE +"."+ROW_ID + " " +
    	    			"ORDER BY " + DATABASE_TABLE +"."+NAME;

        return this.mDb.rawQuery(query, null);
    }

    /**
     * Return a Cursor positioned at the Player that matches the given rowId
     * @param rowId
     * @return Cursor positioned to matching Player, if found
     * @throws SQLException if Player could not be found/retrieved
     */
    public Player getPlayer(long rowId) throws SQLException {
    	String query = 
    			"SELECT " + DATABASE_TABLE +"."+ROW_ID+", "+
    					DATABASE_TABLE +"."+NAME+", "+
    					DATABASE_TABLE +"."+KILLCOUNT+", "+
    					DATABASE_TABLE +"."+TEAM+", "+
    					DATABASE_TABLE +"."+TAGGEDBY+", "+
    					CLAN_TABLE +"."+NAME+" AS "+ CLAN + " " +
    					"FROM " + DATABASE_TABLE +" LEFT JOIN " + CLAN_TABLE + 
    					" ON " + DATABASE_TABLE +"."+CLAN+" = " + CLAN_TABLE +"."+ROW_ID + " " +
    					"WHERE " + DATABASE_TABLE+"."+ROW_ID + "=? " +
    					"ORDER BY " + DATABASE_TABLE +"."+NAME;
    	
    	Player selectedPlayer = null;   	
        Cursor mCursor =

        this.mDb.rawQuery(query, new String[]{Long.toString(rowId)});
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        
        long id = mCursor.getLong(mCursor.getColumnIndex(ROW_ID));
        String name = mCursor.getString(mCursor.getColumnIndex(NAME));
        int tags = mCursor.getInt(mCursor.getColumnIndex(KILLCOUNT));
        String clan = mCursor.getString(mCursor.getColumnIndex(CLAN));
        String team = mCursor.getString(mCursor.getColumnIndex(TEAM));
                
        Cursor tagged = this.getPlayersTaggedBy(id);
        
        selectedPlayer = new Player(id, name, null, tagged, team, tags, clan );
        return selectedPlayer;
    }
    
    public Cursor filterPlayersByName(String constraint) {
    	String query = 
    			"SELECT " + DATABASE_TABLE +"."+ROW_ID+", "+
    					DATABASE_TABLE +"."+NAME+", "+
    					DATABASE_TABLE +"."+KILLCOUNT+", "+
    					DATABASE_TABLE +"."+TEAM+", "+
    					DATABASE_TABLE +"."+TAGGEDBY+", "+
    					CLAN_TABLE +"."+NAME+" AS "+ CLAN + " " +
    					"FROM " + DATABASE_TABLE +" LEFT JOIN " + CLAN_TABLE + 
    					" ON " + DATABASE_TABLE +"."+CLAN+" = " + CLAN_TABLE +"."+ROW_ID + " " +
    					"WHERE " + DATABASE_TABLE+"."+NAME + " like ? " +
    					"ORDER BY " + DATABASE_TABLE +"."+NAME;
    	
    	Cursor mCursor =

    	        this.mDb.rawQuery(query, new String[] {"%" + constraint + "%"});
    	        if (mCursor != null) {
    	            mCursor.moveToFirst();
    	        }
    	        return mCursor;
    }
    
    public Cursor getPlayersTaggedBy(long id) {
    	String query = 
    			"SELECT " + DATABASE_TABLE +"."+ROW_ID+", "+
    					DATABASE_TABLE +"."+NAME+", "+
    					DATABASE_TABLE +"."+KILLCOUNT+", "+
    					DATABASE_TABLE +"."+TEAM+", "+
    					DATABASE_TABLE +"."+TAGGEDBY+", "+
    					CLAN_TABLE +"."+NAME+" AS "+ CLAN + " " +
    					"FROM " + DATABASE_TABLE +" LEFT JOIN " + CLAN_TABLE + 
    					" ON " + DATABASE_TABLE +"."+CLAN+" = " + CLAN_TABLE +"."+ROW_ID + " " +
    					"WHERE " + DATABASE_TABLE+"."+TAGGEDBY + "=? " +
    					"ORDER BY " + DATABASE_TABLE +"."+NAME;
    	Cursor mCursor =

    	        this.mDb.rawQuery(query, new String[] {Long.toString(id)});
    	        if (mCursor != null) {
    	            mCursor.moveToFirst();
    	        }
    	        return mCursor;
    }

  
    public boolean updatePlayer(Long id, String name, Integer count, String team, Long taggedBy, Long clan){
    	ContentValues initialValues = new ContentValues();
        initialValues.put(NAME, name);
        initialValues.put(KILLCOUNT, count);
        initialValues.put(TEAM, team);
        initialValues.put(TAGGEDBY, taggedBy);
        initialValues.put(CLAN, clan);

        return this.mDb.update(DATABASE_TABLE, initialValues, ROW_ID + "=" + id, null) >0; 
    }
    
    public boolean submitKill(Long zombieID, Long humanID){
    	List<NameValuePair> data = new ArrayList<NameValuePair>();
		data.add(new BasicNameValuePair("zombieID", zombieID.toString()));
		data.add(new BasicNameValuePair("humanID", humanID.toString()));
		
		String result = ServerDatabaseConnection.executePHPPost("http://vm444-2.se.rit.edu/SubmitTag.php", data);
		return result.equals("SUCCESS");
        
    }

}